package cn.edu.zjgsu.ito.service;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tools.ant.filters.TokenFilter.Trim;




import cn.edu.zjgsu.ito.model.AdminCourseManageModel;
import cn.edu.zjgsu.ito.model.LoginModel;
import cn.edu.zjgsu.ito.model.TeaScoreInputModel;
import cn.edu.zjgsu.ito.util.GetTimeUtil;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

public class TeaScoreInputService {
	private int flag;
	private String strFlag;
	private int a[]={0,0,0,0};
	public List<Record> lookCourse(String teaId) {
		List<Record> list=Db.find(TeaScoreInputModel.dao.lookCourseSql,teaId);
		if(list==null || list.size()<=0)
		{
			return null;
		}
		else{	
			return list;
		}
	}

	public Record lookTeaName(String teaId) {
		List<Record> list=Db.find(TeaScoreInputModel.dao.lookTeaNameSql,teaId);
		if(list==null || list.size()<=0)
		{
			return null;
		}
		else{	
			return list.get(0);
		}
	}

	public List<Record> lookStudentsInfo(String uuid) throws ParseException {
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		System.out.println("还没有成绩");
		List<Record> list=Db.find(TeaScoreInputModel.dao.lookStudentsInfoSql,uuid,0);
		for(int i=0;i<list.size();i++){
			String []b= new String[100];
			Date []date=new Date[100];
			Date dates=new Date();
			String Dates="";
			List<Record> lists = Db.find("select * from newpassportno where passportNo=?",list.get(i).getStr("passportNo"));
			if(lists.size()>1){
				for(int j=0;j<lists.size();j++){
					b[j] = lists.get(j).getStr("time");
					date[j] = formatter.parse(b[j]);
				}
				System.out.println(lists.size());
				for(int a=0;a<lists.size()-1;a++){
					dates = date[a];
					int c = (date[a+1]).compareTo(dates);
					System.out.println("-------");
					System.out.println(c);
					System.out.println("++++++++++++++++++++++++++++");
					if(c>0){
						dates = date[a+1];
					}
					System.out.println(dates);
					System.out.println("================");
				}
				Dates = formatter.format(dates);
				Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
				list.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
			}
			else{
				Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(i).getStr("passportNo"));
				list.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
			}		

		}
		System.out.println(list.size());
		if(list==null || list.size()<=0)
		{
			return null;
		}
		else{	
			return list;
		}
	}

	public List<Record> lookSaveStudentInfo(String uuid) {
		System.out.println("保存的成绩");
		try{
			List<Record> list=Db.find(TeaScoreInputModel.dao.lookSaveStudentInfoSql,uuid,0);
			System.out.println("学生数"+list.size());
			if(list==null || list.size()<=0)
			{
				return null;
			}
			else{	
				return list;
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	/*	public Page<Record> lookStudentsInfo(String teaName, String courseName,
			int pageNum) {
		Page<Record> list = Db.paginate(pageNum, 2, "select * ", "from student_timetable t,student_info i where t.sNo=i.sNo and t.tName=? and t.courseName=?",teaName,courseName);
		if(list==null || list.getList().size()<=0)
		{
			return null;
		}
		else{	
			return list;
		}
	}*/

	public String insertTemplate(String type, String fileName,
			String fileAddress, String teaName, String createTime) {
		System.out.println("插入模版记录");
		flag=Db.update(TeaScoreInputModel.dao.insertTemplateSql,type,fileName,fileName,fileAddress,teaName,createTime);
		System.out.println("插入结果:"+flag);
		if(flag==1){
			return "ok";
		}
		else{
			return "error";
		}
	}

	public String insertUploadFile(String type, String fileName,
			String fileAddress, String teaName, String createTime) {
		flag=Db.update(TeaScoreInputModel.dao.insertUploadFileSql,type,fileName,fileName,fileAddress,teaName,createTime);
		if(flag==1){
			return "ok";
		}
		else{
			return "error";
		}
	}

	/*public String insertGrade(String[] studentGrade, String course,
			String teaName, int[] a, int num, String flag2) {

		if(flag2.equals("ok")){
			flag=Db.update(TeaScoreInputModel.dao.insertGradeSql,studentGrade[0],studentGrade[3],studentGrade[studentGrade.length-1],teaName,course);

		int x=0;
		for(int m=5;m<=num;m++){
			for(int n=x;n<4;n++){
				if(a[n]!=0){
					switch (n) {
					case 0:
						Db.update(TeaScoreInputModel.dao.insertUsualGradeSql,studentGrade[m],studentGrade[0]);
						x++;
						break;
					case 1:
						Db.update(TeaScoreInputModel.dao.insertExperimentGradeSql,studentGrade[m],studentGrade[0]);
						x++;
						break;
					case 2:
						Db.update(TeaScoreInputModel.dao.insertFinalGradeSql,studentGrade[m],studentGrade[0]);
						x++;
						break;
					case 3:
						Db.update(TeaScoreInputModel.dao.insertOtherGradeSql,studentGrade[m],studentGrade[0]);
						x++;
						break;
					}
					break;
				}
			}
		}
		}
		if(flag==1){
			return "ok";
		}
		else{
			return "error";
		}

	}

	public String lookStudent(String[] studentGrade, String course) {
		List<Record> list=Db.find(TeaScoreInputModel.dao.lookStudentSql,studentGrade[0],course);
		if(list==null || list.size()<=0)
		{
			return "ok";
		}
		else{	
			return "exist";
		}
	}*/

	public int judge(String usual2, String experiment2, String final12,
			String other2,int num, int a[]) {
		if(!usual2.trim().equals("")&&!usual2.trim().equals("0")){
			a[0]=1;
			num++;
		}
		if(!experiment2.trim().equals("")&&!experiment2.trim().equals("0")){
			a[1]=1;
			num++;
		}
		if(!final12.trim().equals("")&&!final12.trim().equals("0")){
			a[2]=1;
			num++;
		}
		if(!other2.trim().equals("")&&!other2.trim().equals("0")){
			a[3]=1;
			num++;
		}
		return num;
	}

	public String getXCellValue(XSSFCell cell) {  
		String cellValue = "";  
		DecimalFormat df = new DecimalFormat("#");  
		switch (cell.getCellType()) {  
		case XSSFCell.CELL_TYPE_STRING:  
			cellValue = cell.getRichStringCellValue().getString().trim();  
			break;  
		case XSSFCell.CELL_TYPE_NUMERIC:  
			cellValue = df.format(cell.getNumericCellValue()).toString();  
			break;  
		case XSSFCell.CELL_TYPE_BOOLEAN:  
			cellValue = String.valueOf(cell.getBooleanCellValue()).trim();  
			break;  
		case XSSFCell.CELL_TYPE_FORMULA:  
			cellValue = cell.getCellFormula();  
			break;  
		default:  
			cellValue = "";  
		}  
		return cellValue;  
	}  

	public String getHCellValue(HSSFCell cell) {  
		String cellValue = "";  
		DecimalFormat df = new DecimalFormat("#");  
		switch (cell.getCellType()) {  
		case HSSFCell.CELL_TYPE_STRING:  
			cellValue = cell.getRichStringCellValue().getString().trim();  
			break;  
		case HSSFCell.CELL_TYPE_NUMERIC:  
			cellValue = df.format(cell.getNumericCellValue()).toString();  
			break;  
		case HSSFCell.CELL_TYPE_BOOLEAN:  
			cellValue = String.valueOf(cell.getBooleanCellValue()).trim();  
			break;  
		case HSSFCell.CELL_TYPE_FORMULA:  
			cellValue = cell.getCellFormula();  
			break;  
		default:  
			cellValue = "";  
		}  
		return cellValue;  
	}

	public String calculate(String usual2, String experiment2, String final12,
			String other2, String gradeSystem2, String[] grades) {
		System.out.println("成绩计算");
		int flag=0;

		//System.out.println(usual2+"a"+experiment2+"b"+final12+"c"+other2+"d");
		BigDecimal bd = null;
		String[] percentage=new String[4];
		int count=0;
		if(!"".equals(usual2.trim())&&!"0".equals(usual2.trim())){
			percentage[count++]=usual2;
		}

		if(!"".equals(experiment2.trim())&&!"0".equals(experiment2.trim())){
			percentage[count++]=experiment2;
		}

		if(!"".equals(final12.trim())&&!"0".equals(final12.trim())){
			percentage[count++]=final12;
		}	

		if(!"".equals(other2.trim())&&!"0".equals(other2.trim())){
			percentage[count++]=other2;
		}

		for(int i=0;i<count;i++){
			if("".equals(grades[i])){
				flag=1;
			}
		}
		if(flag==1){
			System.out.println("该学生无成绩");
			return "";
		}
		else{
			double totalGrade=0;
			if(usual2.trim().equals("")&&experiment2.trim().equals("")&&final12.trim().equals("")&&other2.trim().equals("")){
				System.out.println("全都为空");
				try{
					if(gradeSystem2.equals("五级制")){

					}
					else{
						for(int i=0;i<4;i++){
							try{
								if(Integer.parseInt(grades[i])>=0&&Integer.parseInt(grades[i])<=100){
									totalGrade+=0.25*(Double.parseDouble(grades[i]));	
								}
								else{
									System.out.println("该成绩不在指定范围");
									String error="1";
									return error;
								}
							}catch(Exception e){
								e.printStackTrace();
								String error="2";
								return error;
							}

						}
						bd = new BigDecimal(totalGrade);
						bd = bd.setScale(1, BigDecimal.ROUND_HALF_UP);
						System.out.println(bd);
						return bd+"";
					}
				}catch(Exception e){
					e.printStackTrace();
				}

			}
			else{
				if(gradeSystem2.equals("五级制")){
					for(int i=0;i<count;i++){
						totalGrade+=Double.parseDouble(percentage[i])*translate(grades[i])*0.01;
					}
					//System.out.println(totalGrade+"结束");
					return translate2(totalGrade);
				}
				else{
					for(int i=0;i<count;i++){
						try{
							if(Integer.parseInt(grades[i])>=0&&Integer.parseInt(grades[i])<=100){
								totalGrade+=Double.parseDouble(percentage[i])*Double.parseDouble(grades[i])*0.01;
							}
							else{
								System.out.println("该成绩不在指定范围");
								String error="1";
								return error;
							}
						}catch(Exception e){
							e.printStackTrace();
							String error="2";
							return error;
						}
						//System.out.println(Double.parseDouble(grades[i]));

					}
					bd = new BigDecimal(totalGrade);
					bd = bd.setScale(1, BigDecimal.ROUND_HALF_UP);
					System.out.println(bd);
					return bd+"";
				}
			}
		}


		return null;
	}

	private double translate(String str){
		if(str.equals("A"))
			return 95;
		if(str.equals("B"))
			return 85;
		if(str.equals("C"))
			return 75;
		if(str.equals("P"))
			return 65;
		if(str.equals("F"))
			return 45;
		return 0;
	}

	private String translate2(double totalGrade){
		if(totalGrade>=90){
			return "A";
		}
		if(totalGrade>=80&&totalGrade<90){
			return "B";
		} 
		if(totalGrade>=70&&totalGrade<80){
			return "C";
		} 
		if(totalGrade>=60&&totalGrade<70){
			return "P";
		} 
		if(totalGrade<60){
			return "F";
		}
		return null; 
	}

	public String getGradeJson(String usual, String experiment, String final1,
			String other, String gradeSystem, String gradeInfo, String term, String courseName, String teaId, String type, String uuid, String courseNo, String[] judge) {
		System.out.println("成绩比例:"+usual+experiment+final1+other);
		HashMap<String, String> map=new HashMap<String, String>();
		String[] grades= new String[100];
		String gradeJson="";
		int judgeNum=0;
		if(usual.trim().equals("")&&experiment.trim().equals("")&&final1.trim().equals("")&&other.trim().equals("")){
			usual=experiment=final1=other="25";
		}

		JSONArray jsonArray=JSONArray.fromObject(gradeInfo);
		for(Object obj:jsonArray){

			JSONObject jsonObject=(JSONObject) obj;
			Iterator it=jsonObject.keys();
			while(it.hasNext()){
				String key=String.valueOf(it.next());
				String value=jsonObject.get(key).toString();
				map.put(key, value);
				//System.out.println(map.get(key));
			}
			int j=0;

			if (!"".equals(usual.trim()) && !"0".equals(usual.trim())) {
				grades[j++] = map.get("grade6");
			} else {
				map.put("grade6", "");
			}

			if (!"".equals(experiment.trim()) && !"0".equals(experiment.trim())) {
				grades[j++] = map.get("grade7");
			} else {
				map.put("grade7", "");
			}

			if (!"".equals(final1.trim()) && !"0".equals(final1.trim())) {
				grades[j++] = map.get("grade8");
			} else {
				map.put("grade8", "");
			}
			if (!"".equals(other.trim()) && !"0".equals(other.trim())) {
				grades[j++] = map.get("grade9");
			} else {
				map.put("grade9", "");
			}

			for (int i = 0; i < j; i++) {
				System.out.println("成绩"+i+":"+grades[i]);			
			}
			String totalGrade=calculate(usual,experiment,final1,other,gradeSystem,grades);
			System.out.println("总成绩:"+totalGrade);
			if(totalGrade.equals("1")){
				judge[judgeNum++]="1";
				map.put("grade6", "");
				map.put("grade7", "");
				map.put("grade8", "");
				map.put("grade9", "");
				totalGrade="";
				//return "该成绩不在指定范围";
			}
			else if(totalGrade.equals("2")){
				judge[judgeNum++]="1";
				map.put("grade6", "");
				map.put("grade7", "");
				map.put("grade8", "");
				map.put("grade9", "");
				totalGrade="";
				//return "该成绩不是整数";
			}
			else if(totalGrade.equals("")){
				System.out.println("成绩有空值");
				judge[judgeNum++]="";
				map.put("grade6", "");
				map.put("grade7", "");
				map.put("grade8", "");
				map.put("grade9", "");
			}
			map.put("grade10", totalGrade);
			flag=lookStudentGrade(map.get("grade0"), courseNo,term,uuid);
			System.out.println(flag);
			if(flag==1){
				System.out.println("学生成绩还没有");
				/*if(usual.trim().equals("")&&experiment.trim().equals("")&&final1.trim().equals("")&&other.trim().equals("")){
					usual=experiment=final1=other="25";
				}*/
				flag=method1(map,term,courseName,teaId,type,usual,experiment,final1,other,uuid,courseNo);
				if(flag==0){
					map.put("result","保存成绩失败!");
				}
				else{
					map.put("result","保存成绩成功!");
				}
			}
			else{
				System.out.println("学生成绩已存在");
				if(usual.trim().equals("")&&experiment.trim().equals("")&&final1.trim().equals("")&&other.trim().equals("")){
					usual=experiment=final1=other="25";
				}
				flag=method2(map,courseNo,term,type,usual,experiment,final1,other);
				if(flag==0){
					map.put("result","保存成绩失败!");
				}
				else{
					map.put("result","保存成绩成功!");
				}
			}
			JSONObject json=new JSONObject();
			json.putAll(map);
			gradeJson+=json.toString()+",";
		}	

		return gradeJson;
	}

	private int method1(HashMap<String, String> map, String term,
			String courseName, String teaId, String type, String usual, String experiment, String final1, String other, String uuid, String courseNo) {
		if(type.equals("commit")){
			flag=commitGrade(map,term,courseName,teaId,usual,experiment,final1,other,uuid,courseNo);
			return flag;
		}
		else{
			flag=saveGrade(map,term,courseName,teaId,usual,experiment,final1,other,uuid,courseNo);
			return flag;
		}
	}

	private int method2(HashMap<String, String> map, String courseNo,
			String term, String type, String usual, String experiment, String final1, String other) {
		if(type.equals("commit")){
			flag=updateCommitGrade(map,courseNo,term,usual,experiment,final1,other);
			return flag;
		}
		else{
			flag=updateSaveGrade(map,courseNo,term,usual,experiment,final1,other);
			return flag;
		}
	}

	private int saveGrade(HashMap<String, String> map, String term,
			String courseName, String teaId, String usual, String experiment, String final1, String other, String uuid, String courseNo) {
		Record r=Db.findFirst(TeaScoreInputModel.dao.findSNoByPassportNo,map.get("grade0"));

		try{
			flag=Db.update(TeaScoreInputModel.dao.commitGradeSql,map.get("grade0"),r.getStr("majorNo"),map.get("grade3"),map.get("grade6"),map.get("grade7"),map.get("grade8"),map.get("grade9"),map.get("grade10"),term,courseNo,courseName,teaId,0,usual,experiment,final1,other,uuid);
		}
		catch(Exception e){
			System.out.println(e);
		}
		return flag;
	}

	private int updateSaveGrade(HashMap<String, String> map, String courseNo,
			String term, String usual, String experiment, String final1, String other) {
		flag=Db.update(TeaScoreInputModel.dao.updateCommitGradeSql,map.get("grade6"),map.get("grade7"),map.get("grade8"),map.get("grade9"),map.get("grade10"),0,usual,experiment,final1,other,map.get("grade0"),courseNo,term);
		return flag;
	}

	private int lookStudentGrade(String passportNo, String courseNo, String term, String uuid) {
		List<Record> list=Db.find(TeaScoreInputModel.dao.lookStudentGradeSql,passportNo,courseNo,term,uuid);
		if(list==null || list.size()<=0)
		{
			return 1;
		}
		else{	
			return 0;
		}
	}

	private int updateCommitGrade(HashMap<String, String> map,
			String courseNo, String term, String usual, String experiment, String final1, String other) {
		flag=Db.update(TeaScoreInputModel.dao.updateCommitGradeSql,map.get("grade6"),map.get("grade7"),map.get("grade8"),map.get("grade9"),map.get("grade10"),1,usual,experiment,final1,other,map.get("grade0"),courseNo,term);
		return flag;
	}

	private int commitGrade(HashMap<String, String> map, String term,
			String courseName, String teaId, String usual, String experiment, String final1, String other, String uuid, String courseNo) {
		Record r=Db.findFirst(TeaScoreInputModel.dao.findSNoByPassportNo,map.get("grade0"));
		System.out.println(r.getStr("passportNo"));
		flag=Db.update(TeaScoreInputModel.dao.commitGradeSql,r.getStr("passportNo"),r.getStr("majorNo"),map.get("grade3"),map.get("grade6"),map.get("grade7"),map.get("grade8"),map.get("grade9"),map.get("grade10"),term,courseNo,courseName,teaId,1,usual,experiment,final1,other,uuid);
		return flag;
	}

	public List<Record> lookGradeStatus(String uuid) {
		List<Record> list=Db.find(TeaScoreInputModel.dao.lookGradeStatusSql,uuid);
		if(list==null || list.size()<=0)
		{
			return null;
		}
		else{	
			return list;
		}
	}

	public List<Record> lookGradeInfo(String uuid) {
		List<Record> list=Db.find(TeaScoreInputModel.dao.lookGradeInfoSql,uuid,0);
		if(list==null || list.size()<=0)
		{
			return null;
		}
		else{	
			return list;
		}
	}

	private Record lookteaTimetable(String uuid) {
		List<Record> list=Db.find(TeaScoreInputModel.dao.lookteaTimetableSql,uuid);
		if(list==null || list.size()<=0)
		{
			return null;
		}
		else{	
			return list.get(0);
		}
	}

	public XSSFWorkbook createWorkbook(List<Record> r2) {
		System.out.println("创建excel");
		double max=0;
		double min=0;
		int actualNum=0;
		for(int i=0;i<r2.size();i++){
			if(r2.get(i).getStr("grade")!=null&&!"".equals(r2.get(i).getStr("grade").trim())){
				max=Double.parseDouble(r2.get(i).getStr("grade"));
				min=Double.parseDouble(r2.get(i).getStr("grade"));
				break;
			}
		}

		double sum=0;
		double ave=0;

		for(int i=0;i<r2.size();i++){
			if(r2.get(i).getStr("grade")!=null&&!"".equals(r2.get(i).getStr("grade").trim())){
				actualNum++;
			}
		}
		System.out.println("实际人数"+actualNum);
		for (int i = 0; i < r2.size(); i++) {   
			if(r2.get(i).getStr("grade")!=null&&!"".equals(r2.get(i).getStr("grade").trim())){
				if (min > Double.parseDouble(r2.get(i).getStr("grade"))) 
					min = Double.parseDouble(r2.get(i).getStr("grade"));   
				if (max < Double.parseDouble(r2.get(i).getStr("grade"))) 
					max = Double.parseDouble(r2.get(i).getStr("grade"));   
			}

		}        
		System.out.println("max的值为" + max + "min的值为" + min);    

		for (int i = 0; i < r2.size(); i++) {
			if(r2.get(i).getStr("grade")!=null&&!"".equals(r2.get(i).getStr("grade").trim())){
				sum+=Double.parseDouble(r2.get(i).getStr("grade"));
			}
		}
		ave=sum/r2.size();
		BigDecimal bd = new BigDecimal(ave);
		bd = bd.setScale(1, BigDecimal.ROUND_HALF_UP);
		System.out.println(bd);

		Record r = lookTeaName(r2.get(0).getStr("tNo"));
		String teaName = r.get("tName");

		Record teaTimetable = lookteaTimetable(r2.get(0).getStr("uuid"));
		System.out.println(teaName);
		System.out.println(teaTimetable.getStr("classroomNo"));
		XSSFWorkbook workbook = new XSSFWorkbook();
		// 创建一个工作表
		XSSFSheet sheet = workbook.createSheet();
		// 创建第一行
		XSSFRow row0 = sheet.createRow(0);
		XSSFCell cell = null;
		// 插入第一行excel表头
		cell = row0.createCell(0);
		cell.setCellValue("课程信息");
		cell = row0.createCell(1);
		cell.setCellValue(r2.get(0).getStr("courseName"));
		cell = row0.createCell(3);
		cell.setCellValue("授课教师");
		cell = row0.createCell(4);
		cell.setCellValue(teaName);

		XSSFRow row1 = sheet.createRow(1);
		cell = row1.createCell(0);
		cell.setCellValue("起止周");
		cell = row1.createCell(1);
		cell.setCellValue(teaTimetable.getStr("startWeek")+"-"+teaTimetable.getStr("endWeek"));
		cell = row1.createCell(3);
		cell.setCellValue("上课时间");
		cell = row1.createCell(4);
		cell.setCellValue(teaTimetable.getStr("whichDay"));

		XSSFRow row2 = sheet.createRow(2);
		cell = row2.createCell(0);
		cell.setCellValue("应考人数");
		cell = row2.createCell(1);
		cell.setCellValue(r2.size());
		cell = row2.createCell(3);
		cell.setCellValue("实考人数");
		cell = row2.createCell(4);
		cell.setCellValue(actualNum);

		XSSFRow row3 = sheet.createRow(3);
		cell = row3.createCell(0);
		cell.setCellValue("最高分");
		cell = row3.createCell(1);
		cell.setCellValue(max);
		cell = row3.createCell(3);
		cell.setCellValue("最低分");
		cell = row3.createCell(4);
		cell.setCellValue(min);
		cell = row3.createCell(6);
		cell.setCellValue("平均分");
		cell = row3.createCell(7);
		cell.setCellValue(bd+"");
		//插入表格表头
		XSSFRow row = sheet.createRow(4);
		cell = row.createCell(0);
		cell.setCellValue("序号");
		cell = row.createCell(1);
		cell.setCellValue("学号");
		cell = row.createCell(2);
		cell.setCellValue("护照号");
		cell = row.createCell(3);
		cell.setCellValue("姓");
		cell = row.createCell(4);
		cell.setCellValue("名");

		int x = 0;
		int y=0;
		int num = 4;
		num = judge(r2.get(0).getStr("dailyPercent"), r2.get(0).getStr("experimentalPercent"), r2.get(0).getStr("finalPercent"), r2.get(0).getStr("otherPercent"), num, a);
		for (int j = 0; j < a.length; j++) {
			System.out.println(a[j]);
		}
		//System.out.println(courseName+term);

		for (int m = 5; m <= num; m++) {
			cell = row.createCell(m);
			for (int n = x; n < 4; n++) {
				if (a[n] != 0) {
					switch (n) {
					case 0:
						cell.setCellValue("平时成绩");
						x = n + 1;
						break;
					case 1:
						cell.setCellValue("实验成绩");
						x = n + 1;
						break;
					case 2:
						cell.setCellValue("期末成绩");
						x = n + 1;
						break;
					case 3:
						cell.setCellValue("其他成绩");
						x = n + 1;
						break;
					}

					break;
				}
				// System.out.println("加"+x);
			}
		}
		cell=row.createCell(num+1);
		cell.setCellValue("总评");

		for(int i=0;i<r2.size();i++){
			y=0;
			XSSFRow nextrow=sheet.createRow(i+5);
			XSSFCell cell2=nextrow.createCell(0);
			cell2.setCellValue(i+1);
			cell2=nextrow.createCell(1);
			if(r2.get(i).get("sNo")==null){
				r2.get(i).set("sNo", "");
			}
			cell2.setCellValue(r2.get(i).get("sNo").toString());
			cell2=nextrow.createCell(2);
			cell2.setCellValue(r2.get(i).get("passportNo").toString());
			cell2=nextrow.createCell(3);
			cell2.setCellValue(r2.get(i).get("firstName").toString());
			cell2=nextrow.createCell(4);
			cell2.setCellValue(r2.get(i).get("lastName").toString());
			/*for(int m=5;m<=num;m++){
				cell2=nextrow.createCell(m);
				cell2.setCellValue("a");
				cell2.setCellValue("a");
				cell2.setCellValue("a");
			}*/
			for(int m=5;m<=num;m++){

				cell2=nextrow.createCell(m);
				for(int n=y;n<4;n++){

					if(a[n]!=0){
						switch (n) {
						case 0:
							cell2.setCellValue(r2.get(i).get("dailyGrade").toString());
							y=n+1;
							break;
						case 1:
							cell2.setCellValue(r2.get(i).get("experimentalGrade").toString());
							y=n+1;
							break;
						case 2:
							cell2.setCellValue(r2.get(i).get("finalGrade").toString());
							y=n+1;						
							break;
						case 3:
							cell2.setCellValue(r2.get(i).get("otherGrade").toString());
							y=n+1;
							break;
						}

						break;
					}
					//System.out.println("加"+x);
				}
			}
			cell2=nextrow.createCell(num+1);
			cell2.setCellValue(r2.get(i).get("grade").toString());
		}

		return workbook;
	}

	public String lookcourseName(String courseNo) {
		List<Record> list=Db.find(TeaScoreInputModel.dao.lookcourseNameSql,courseNo);
		if(list==null || list.size()<=0)
		{
			return null;
		}
		else{	
			return list.get(0).getStr("courseName");
		}
	}

	public List<Record> removeRepeat(List<Record> r) {
		List<Record> handleCourse=null;


		int flag=0;
		for(int i=0;i<r.size();i++){
			List<Record> list1=Db.find(TeaScoreInputModel.dao.lookRosterSql,r.get(i).getStr("uuid"));

			List<String> a = new ArrayList<String>();
			for(int x=0;x<list1.size();x++){	
				a.add(list1.get(x).getStr("passportNo"));
			}
			for (int j = i+1; j < r.size(); j++) {
				List<Record> list2=Db.find(TeaScoreInputModel.dao.lookRosterSql,r.get(j).getStr("uuid"));

				List<String> b = new ArrayList<String>();
				for(int y=0;y<list2.size();y++){
					b.add(list2.get(y).getStr("passportNo"));
				}
				boolean judge=judge(a,b);
				//System.out.println(i+""+j+""+":"+judge);
				if(judge){
					flag=1;
					r.remove(j);
					j--;
				}
			}

		}
		return r;
	}

	private static boolean judge(List<String> a, List<String> b) {
		int flag1=0;
		int flag2=0;
		for (int i = 0; i < a.size(); i++) {
			//System.out.println(a.get(i));
			if(!b.contains(a.get(i))){
				flag1=1;
			}
		}
		for (int i = 0; i < b.size(); i++) {
			//System.out.println(b.get(i));
			if(!a.contains(b.get(i))){
				flag2=1;
			}
		}
		//System.out.println("判断:"+flag1 +   "判断2:"+flag2);
		if(flag1==1||flag2==1){
			return false;
		}
		return true;
	}

	public String lookCourseNo(String courseNo) {
		List<Record> list=Db.find(TeaScoreInputModel.dao.lookCourseNoSql,courseNo);
		System.out.println("截止日期:"+list.size());
		if(list==null || list.size()<=0)
		{
			return null;
		}
		else{	
			return list.get(0).getStr("endDate");
		}
	}

	//教师成绩录入----杨昆鹏
	public List<Record> gradeSelected(String uuid,String testType,String testLock) throws ParseException{
		//第一次录入成绩
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		if(testLock.equals("0")){
			if(testType.equals("1")){
				List<Record> allStu = Db.find("select * from student_timetable,student_info "
						+ "where student_timetable.passportNo=student_info.passportNo and uuid=?",uuid);
				for(int i=0;i<allStu.size();i++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> allStus = Db.find("select * from newpassportno where passportNo=?",allStu.get(i).getStr("passportNo"));
					if(allStus.size()>1){
						for(int j=0;j<allStus.size();j++){
							b[j] = allStus.get(j).getStr("time");
							date[j] = formatter.parse(b[j]);
						}
						System.out.println(allStus.size());
						for(int a=0;a<allStus.size()-1;a++){
							dates = date[a];
							int c = (date[a+1]).compareTo(dates);
							System.out.println("-------");
							System.out.println(c);
							System.out.println("++++++++++++++++++++++++++++");
							if(c>0){
								dates = date[a+1];
							}
							System.out.println(dates);
							System.out.println("================");
						}
						System.out.println("qqqqqqqqq");
						System.out.println(dates);
						System.out.println("444444444");
						Dates = formatter.format(dates);
						System.out.println(Dates);
						Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						allStu.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
					else{
						System.out.println("[][][][][][][][][][]");
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",allStu.get(i).getStr("passportNo"));
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						allStu.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}		

				}
				return allStu;
			}else if(testType.equals("2")){
				Record test = Db.findFirst("select * from test where uuid=? and testType=?",uuid,testType);
				String testNo = test.getStr("testNo");
				List<Record> repStu = Db.find("select * from student_grade,student_info where student_grade.passportNo=student_info.passportNo and testNo=?",testNo);
				for(int i=0;i<repStu.size();i++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> repStus = Db.find("select * from newpassportno where passportNo=?",repStu.get(i).getStr("passportNo"));
					if(repStus.size()>1){
						for(int j=0;j<repStus.size();j++){
							b[j] = repStus.get(j).getStr("time");
							date[j] = formatter.parse(b[j]);
						}
						System.out.println(repStus.size());
						for(int a=0;a<repStus.size()-1;a++){
							dates = date[a];
							int c = (date[a+1]).compareTo(dates);
							System.out.println("-------");
							System.out.println(c);
							System.out.println("++++++++++++++++++++++++++++");
							if(c>0){
								dates = date[a+1];
							}
							System.out.println(dates);
							System.out.println("================");
						}
						System.out.println("qqqqqqqqq");
						System.out.println(dates);
						System.out.println("444444444");
						Dates = formatter.format(dates);
						System.out.println(Dates);
						Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						repStu.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
					else{
						System.out.println("[][][][][][][][][][]");
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",repStu.get(i).getStr("passportNo"));
						System.out.println("111111111");
						System.out.println(newpassportNo.getStr("newpassportNo"));
						repStu.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}		

				}
				return repStu;
			}
			//已经有了成绩
		}else{
			Record test = Db.findFirst("select * from test where uuid=? and testType=?",uuid,testType);
			String testNo = test.getStr("testNo");
			List<Record> allStu = Db.find("select * from student_grade,student_info where student_grade.passportNo=student_info.passportNo and testNo=?",testNo);
			for(int i=0;i<allStu.size();i++){
				String []b= new String[100];
				Date []date=new Date[100];
				Date dates=new Date();
				String Dates="";
				List<Record> allStus = Db.find("select * from newpassportno where passportNo=?",allStu.get(i).getStr("passportNo"));
				if(allStus.size()>1){
					for(int j=0;j<allStus.size();j++){
						b[j] = allStus.get(j).getStr("time");
						date[j] = formatter.parse(b[j]);
					}
					System.out.println(allStus.size());
					for(int a=0;a<allStus.size()-1;a++){
						dates = date[a];
						int c = (date[a+1]).compareTo(dates);
						System.out.println("-------");
						System.out.println(c);
						System.out.println("++++++++++++++++++++++++++++");
						if(c>0){
							dates = date[a+1];
						}
						System.out.println(dates);
						System.out.println("================");
					}
					System.out.println("qqqqqqqqq");
					System.out.println(dates);
					System.out.println("444444444");
					Dates = formatter.format(dates);
					System.out.println(Dates);
					Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
					System.out.println("111111111");
					System.out.println(newpassportNo.getStr("newpassportNo"));
					allStu.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
				}
				else{
					System.out.println("[][][][][][][][][][]");
					Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",allStu.get(i).getStr("passportNo"));
					System.out.println("111111111");
					System.out.println(newpassportNo.getStr("newpassportNo"));
					allStu.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
				}		

			}
			return allStu;
		}

		return null;
	}

	public List<Record> index(String tNo){
		List<Record> allTest = Db.find("select * from test,course_end where test.testNo=course_end.testNo and tNo=?",tNo);
		return allTest;
	}

	public void saveGrades(String[] grades,String testNo,String oness,String twoss,String threess,String fourss,String regime,String tNo){
		List<Record> temp = Db.find("select * from student_grade where testNo=?",testNo);
		Record testInf = Db.findFirst("select * from test where testNo=?",testNo);
		String uuid = testInf.getStr("uuid");
		List<Record> allStu = Db.find("select * from student_timetable,student_info "
				+ "where student_timetable.passportNo=student_info.passportNo and uuid=?",uuid);
		if(temp.size() != 0){
			Db.update("delete from student_grade where testNo=?",testNo);
		}
		for(int i = 0;i < allStu.size();i++){
			String termValue = allStu.get(i).getStr("termValue");
			String passportNo = allStu.get(i).getStr("passportNo");
			String majorNo = allStu.get(i).getStr("majorNo");
			String majorName = allStu.get(i).getStr("majorName");
			String courseNo = allStu.get(i).getStr("courseNo");
			String courseName = allStu.get(i).getStr("courseName");
			System.out.println("字符串！！！！！！！");
			String oneGra = "";
			String twoGra = "";
			String threeGra = "";
			String fourGra = "";
			String finalGrade = "";
			int one = Integer.valueOf(oness);
			int two = Integer.valueOf(twoss);
			int three = Integer.valueOf(threess);
			int four = Integer.valueOf(fourss);
			System.out.println("空的比例"+ two + "空的比例" + three);
			for(int j = 0;j < grades.length ;j++){
				if(passportNo.equals(grades[j])){
					if(!grades[j+1].equals("00")){

						oneGra = grades[j+1];
					}
					if(!grades[j+2].equals("00")){

						twoGra = grades[j+2];
					}
					if(!grades[j+3].equals("00")){

						threeGra = grades[j+3];
					}
					if(!grades[j+4].equals("00")){

						fourGra = grades[j+4];
					}
				}
			}
			String grade[] = {oneGra,twoGra,threeGra,fourGra};
			System.out.println("学生成绩："+grade[0]+grade[1]+grade[2]+grade[3]);
			if(one == 0){
				grade[0] = "0";
				oneGra = "";
			}
			if(two == 0){
				grade[1] = "0";
				twoGra = "";
			}
			if(three == 0){
				grade[2] = "0";
				threeGra = "";
			}
			if(four == 0){
				grade[3] = "0";
				fourGra = "";
			}
			if(regime.equals("五级制")){
				System.out.println("五级制");
				int intGrade[] = new int[4];
				int gradeTemp = 0;
				for(int x = 0;x < 4;x++){
					switch(grade[x]){
					case "A" :
						grade[x] = "90";
						break;
					case "B" :
						grade[x] = "80";
						break;
					case "C" :
						grade[x] = "70";
						break;
					case "P" :
						grade[x] = "60";
						break;
					case "F":
						grade[x] = "50";
						break;
					case "作弊":
						finalGrade = "F";
						continue;
					case "旷考":
						grade[x] = "0";
						break;
					case "0":
						grade[x] = "0";
						break;
					default :
						grade[x] = "";
						break;
					}
					try{
						gradeTemp = Integer.valueOf(grade[x]);
						intGrade[x] = gradeTemp;
					}catch(NumberFormatException e){
						finalGrade = "未录入";
					}
				}
				if(finalGrade.equals("")){

					System.out.println(intGrade[0]+intGrade[1]+intGrade[2]+intGrade[3]+"学生的成绩");

					int finalGra = (one*intGrade[0] + two*intGrade[1] + three*intGrade[2] + four*intGrade[3])/100;
					if(finalGrade.equals("")){
						if( finalGra >= 90){
							finalGrade = "A";
						}else if(finalGra >= 80){
							finalGrade = "B";
						}else if(finalGra >= 70){
							finalGrade = "C";
						}else if(finalGra >= 60){
							finalGrade = "P";
						}else if(finalGra < 60){
							finalGrade = "F";
						}
					}

				}
			}else if(regime.equals("百分制")){
				try{
					int intGrade[] = new int[4];
					if(grade[3].equals("旷考")){
						grade[3] = "0";
					}
					if(!grade[3].equals("作弊")){
						for(int x = 0;x < grade.length;x++){
							intGrade[x] = Integer.valueOf(grade[x]);
						}
						int finalGra = (one*intGrade[0] + two*intGrade[1] + three*intGrade[2] + four*intGrade[3])/100;
						finalGrade = finalGra + "";
					}else{
						finalGrade = "0";
					}
				}catch(NumberFormatException e){

					finalGrade = "未录入";
				}
			}
			Record oneStuGra = new Record().set("termValue",termValue).set("passportNo",passportNo)
					.set("majorName",majorName).set("majorNo",majorNo).set("courseNo",courseNo).set("courseName",courseName)
					.set("dailyGrade",oneGra).set("experimentalGrade",twoGra).set("otherGrade",threeGra).set("finalGrade",fourGra)
					.set("grade",finalGrade).set("dailyPercent",oness).set("experimentalPercent",twoss)
					.set("otherPercent",threess).set("finalPercent",fourss)
					.set("tNo",tNo).set("gradeStatus",1).set("uuid",uuid).set("testNo",testNo);
			Db.update("update test set testLock='3' where testNo=?",testNo);
			Db.save("student_grade",oneStuGra);
		}
	}

	//提交成绩
	public void submitGrades(String[] grades,String testNo,String oness,String twoss,String threess,String fourss,String regime,String tNo){
		List<Record> temp = Db.find("select * from student_grade where testNo=?",testNo);
		Record testInf = Db.findFirst("select * from test where testNo=?",testNo);
		String uuid = testInf.getStr("uuid");
		List<Record> allStu = Db.find("select * from student_timetable,student_info "
				+ "where student_timetable.passportNo=student_info.passportNo and uuid=?",uuid);
		if(temp.size() != 0){
			Db.update("delete from student_grade where testNo=?",testNo);
		}
		for(int i = 0;i < allStu.size();i++){
			String termValue = allStu.get(i).getStr("termValue");
			String passportNo = allStu.get(i).getStr("passportNo");
			String majorNo = allStu.get(i).getStr("majorNo");
			String majorName = allStu.get(i).getStr("majorName");
			String courseNo = allStu.get(i).getStr("courseNo");
			String courseName = allStu.get(i).getStr("courseName");
			System.out.println("字符串！！！！！！！");
			String oneGra = "";
			String twoGra = "";
			String threeGra = "";
			String fourGra = "";
			String finalGrade = "";
			int one = Integer.valueOf(oness);
			int two = Integer.valueOf(twoss);
			int three = Integer.valueOf(threess);
			int four = Integer.valueOf(fourss);
			System.out.println("空的比例"+ two + "空的比例" + three);
			for(int j = 0;j < grades.length ;j++){
				if(passportNo.equals(grades[j])){
					if(!grades[j+1].equals("00")){

						oneGra = grades[j+1];
					}
					if(!grades[j+2].equals("00")){

						twoGra = grades[j+2];
					}
					if(!grades[j+3].equals("00")){

						threeGra = grades[j+3];
					}
					if(!grades[j+4].equals("00")){

						fourGra = grades[j+4];
					}
				}
			}
			String grade[] = {oneGra,twoGra,threeGra,fourGra};
			System.out.println("学生成绩："+grade[0]+grade[1]+grade[2]+grade[3]);
			if(one == 0){
				grade[0] = "0";
				oneGra = "";
			}
			if(two == 0){
				grade[1] = "0";
				twoGra = "";
			}
			if(three == 0){
				grade[2] = "0";
				threeGra = "";
			}
			if(four == 0){
				grade[3] = "0";
				fourGra = "";
			}
			if(regime.equals("五级制")){
				System.out.println("五级制");
				int intGrade[] = new int[4];
				int gradeTemp = 0;
				for(int x = 0;x < 4;x++){
					switch(grade[x]){
					case "A" :
						grade[x] = "90";
						break;
					case "B" :
						grade[x] = "80";
						break;
					case "C" :
						grade[x] = "70";
						break;
					case "P" :
						grade[x] = "60";
						break;
					case "F":
						grade[x] = "50";
						break;
					case "作弊":
						finalGrade = "F";
						continue;
					case "旷考":
						grade[x] = "0";
						break;
					case "0":
						grade[x] = "0";
						break;
					default :
						grade[x] = "";
						break;
					}
					try{
						gradeTemp = Integer.valueOf(grade[x]);
						intGrade[x] = gradeTemp;
					}catch(NumberFormatException e){
						finalGrade = "未录入";
					}
				}
				if(finalGrade.equals("")){

					System.out.println(intGrade[0]+intGrade[1]+intGrade[2]+intGrade[3]+"学生的成绩");

					int finalGra = (one*intGrade[0] + two*intGrade[1] + three*intGrade[2] + four*intGrade[3])/100;
					if(finalGrade.equals("")){
						if( finalGra >= 90){
							finalGrade = "A";
						}else if(finalGra >= 80){
							finalGrade = "B";
						}else if(finalGra >= 70){
							finalGrade = "C";
						}else if(finalGra >= 60){
							finalGrade = "P";
						}else if(finalGra < 60){
							finalGrade = "F";
						}
					}

				}
			}else if(regime.equals("百分制")){
				try{
					int intGrade[] = new int[4];
					if(grade[3].equals("旷考")){
						grade[3] = "0";
					}
					if(!grade[3].equals("作弊")){
						for(int x = 0;x < grade.length;x++){
							intGrade[x] = Integer.valueOf(grade[x]);
						}
						int finalGra = (one*intGrade[0] + two*intGrade[1] + three*intGrade[2] + four*intGrade[3])/100;
						finalGrade = finalGra + "";
					}else{
						finalGrade = "0";
					}
				}catch(NumberFormatException e){

					finalGrade = "未录入";
				}
			}
			Record oneStuGra = new Record().set("termValue",termValue).set("passportNo",passportNo)
					.set("majorName",majorName).set("majorNo",majorNo).set("courseNo",courseNo).set("courseName",courseName)
					.set("dailyGrade",oneGra).set("experimentalGrade",twoGra).set("otherGrade",threeGra).set("finalGrade",fourGra)
					.set("grade",finalGrade).set("dailyPercent",oness).set("experimentalPercent",twoss)
					.set("otherPercent",threess).set("finalPercent",fourss)
					.set("tNo",tNo).set("gradeStatus",1).set("uuid",uuid).set("testNo",testNo);
			Db.update("update test set testLock='1' where testNo=?",testNo);
			Db.save("student_grade",oneStuGra);
		}
	}

	public void saveGradesRep(String[] grades, String testNo, String teaId) {

		List<Record> temp = Db.find("select * from student_grade where testNo=?",testNo);
		System.out.println("补考成绩录入开始!!!!!!!!!!!");
		for(int i = 0;i < temp.size();i++){
			String passportNo = temp.get(i).getStr("passportNo");
			for(int j = 0;j < grades.length;j++){
				if(passportNo.equals(grades[j])){
					Db.update("update student_grade set grade=? where testNo=? and passportNo=?",grades[j+1],testNo,passportNo);
					Db.update("update test set testLock='3' where testNo=?",testNo);
				}
			}

		}
	}

	public void submitGradesRep(String[] grades, String testNo, String teaId) {

		List<Record> temp = Db.find("select * from student_grade where testNo=?",testNo);
		System.out.println("补考成绩录入开始!!!!!!!!!!!");
		for(int i = 0;i < temp.size();i++){
			String passportNo = temp.get(i).getStr("passportNo");
			for(int j = 0;j < grades.length;j++){
				if(passportNo.equals(grades[j])){
					Db.update("update student_grade set grade=? where testNo=? and passportNo=?",grades[j+1],testNo,passportNo);
					Db.update("update test set testLock='1' where testNo=?",testNo);
				}
			}

		}
	}

	public int setGrade(String ones,String twos,String threes,String fours) {
		int one = Integer.valueOf(ones);
		int two = Integer.valueOf(twos);
		int three = Integer.valueOf(threes);
		int four = Integer.valueOf(fours);
		int temp = one + two + three + four;
		if(temp == 100){
			return 1;
		}else{
			return 0;
		}

	}

	public int selectTestLock(String testNo) {
		Record testInf = Db.findFirst("select * from test where testNo=?",testNo);
		String testLock = testInf.getStr("testLock");
		if(testLock.equals("0")){
			return 0;
		}else{
			return 1;
		}
	}

	public void setGradeBi(String testNo,String one,String two,String three,String four) {
		Db.update("update student_grade set dailyPercent=?,experimentalPercent=?,"
				+ "otherPercent=?,finalPercent=? where testNo=?",one,two,three,four,testNo);

	}

	public List<Record> gradePraSelected(String testNo) throws ParseException {
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		List<Record> student = Db.find("select * from student_grade,student_info where student_grade.passportNo=student_info.passportNo and testNo=?",testNo);
		for(int i=0;i<student.size();i++){
			String []b= new String[100];
			Date []date=new Date[100];
			Date dates=new Date();
			String Dates="";
			List<Record> students = Db.find("select * from newpassportno where passportNo=?",student.get(i).getStr("passportNo"));			
			if(students.size()>1){
				for(int j=0;j<students.size();j++){
					b[j] = students.get(j).getStr("time");
					date[j] = formatter.parse(b[j]);
				}
				System.out.println(students.size());
				for(int a=0;a<students.size()-1;a++){
					dates = date[a];
					int c = (date[a+1]).compareTo(dates);						
					if(c>0){
						dates = date[a+1];
					}

				}								
				Dates = formatter.format(dates);
				System.out.println(Dates);
				Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
				System.out.println(newpassportNo.getStr("newpassportNo"));
				student.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
			}
			else{
				System.out.println("[][][][][][][][][][]");
				Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",student.get(i).getStr("passportNo"));
				System.out.println("111111111");
				System.out.println(newpassportNo.getStr("newpassportNo"));
				student.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
			}		

		}
		return student;
	}

	public void exportGrade(String testNo,OutputStream out) {

		/*//创建一个Excel工作薄
		HSSFWorkbook book = new HSSFWorkbook();
		//在Excel工作薄中建立一张工作表
		HSSFSheet sheet = book.createSheet("学生成绩");
		//设置单元格格式（文本）     标题
		HSSFCellStyle cellStyle = book.createCellStyle();	
		//设计这些格式
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		//设置字体
		HSSFFont font = book.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short)16);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
		cellStyle.setFont(font);


		HSSFCellStyle cellStyle1 = book.createCellStyle();
		cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
//		cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
//		cellStyle.setWrapText(true);//自动换行
		//设置列宽
		sheet.setColumnWidth(1,20*255);
		sheet.setColumnWidth(2,20*255);
		sheet.setColumnWidth(3,20*255);
		sheet.setColumnWidth(4,20*255);
		sheet.setColumnWidth(5,11*255);
		sheet.setColumnWidth(6,11*255);
		sheet.setColumnWidth(7,11*255);
		sheet.setColumnWidth(8,11*255);
		sheet.setColumnWidth(9,11*255);
		sheet.setColumnWidth(10,11*255);
		List<Record> student = Db.find("select * from student_grade,student_info,teacher_info "
				+ "where student_grade.passportNo=student_info.passportNo and student_grade.tNo=teacher_info.tNo and testNo=?",testNo);

		 CellRangeAddress cra = new CellRangeAddress(0, 1, 0, 10);
		//第一行为标题
		HSSFRow row0 = sheet.createRow(0);//创建第一行
		row0.setHeight((short) (3*256));//行高
		HSSFCell cell01 = row0.createCell(0);
		HSSFCell cell02 = row0.createCell(1);
		HSSFCell cell03 = row0.createCell(3);
		HSSFCell cell04 = row0.createCell(4);
		HSSFCell cell05 = row0.createCell(6);
		HSSFCell cell06 = row0.createCell(7);
		//定义单元格字符串类型
		cell01.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell02.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell03.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell04.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell05.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell06.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell01.setCellStyle(cellStyle);
		cell02.setCellStyle(cellStyle);
		cell03.setCellStyle(cellStyle);
		cell04.setCellStyle(cellStyle);
		cell05.setCellStyle(cellStyle);
		cell06.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell01.setCellValue("学期：");
		cell02.setCellValue(student.get(0).getStr("termValue"));
		cell03.setCellValue("课程名：");
		cell04.setCellValue(student.get(0).getStr("courseName"));
		cell05.setCellValue("教师：");
		cell06.setCellValue(student.get(0).getStr("tName"));
		//第二行 获取标题对应的数据
		HSSFRow row1 = sheet.createRow(1);//创建第二行
		row1.setHeight((short) (3*256));//行高
		HSSFCell cell11 = row1.createCell(0);
		HSSFCell cell12 = row1.createCell(1);
		HSSFCell cell13 = row1.createCell(2);
		HSSFCell cell14 = row1.createCell(3);
		HSSFCell cell15 = row1.createCell(4);
		HSSFCell cell16 = row1.createCell(5);
		HSSFCell cell17 = row1.createCell(6);
		HSSFCell cell18 = row1.createCell(7);
		HSSFCell cell19 = row1.createCell(8);
		HSSFCell cell110 = row1.createCell(9);
		HSSFCell cell111 = row1.createCell(10);
		HSSFCell cell112 = row1.createCell(11);
		//定义单元格字符串类型
		cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell12.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell13.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell14.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell15.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell16.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell17.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell18.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell19.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell110.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell111.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell112.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell11.setCellStyle(cellStyle);
		cell12.setCellStyle(cellStyle);
		cell13.setCellStyle(cellStyle);
		cell14.setCellStyle(cellStyle);
		cell15.setCellStyle(cellStyle);
		cell16.setCellStyle(cellStyle);
		cell17.setCellStyle(cellStyle);
		cell18.setCellStyle(cellStyle);
		cell19.setCellStyle(cellStyle);
		cell110.setCellStyle(cellStyle);
		cell111.setCellStyle(cellStyle);
		cell112.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell11.setCellValue("序号");
		cell12.setCellValue("护照号"); 
		cell13.setCellValue("姓");
		cell14.setCellValue("名");
		cell15.setCellValue("专业");
		cell16.setCellValue("入学年份");
		cell17.setCellValue("入学季度");
		cell18.setCellValue("平时成绩");
		cell19.setCellValue("实验成绩");
		cell110.setCellValue("其他成绩");
		cell111.setCellValue("期末成绩");
		cell112.setCellValue("总评成绩");
		//学生成绩循环输出
		for(int i = 0;i < student.size();i++){

			HSSFRow rowfor = sheet.createRow(i+2);

			String no = i+1 + "";
			String oldpassportNo = student.get(i).getStr("passportNo");
			String passportNo = Db.findFirst("select * from newpassportno where passportNo=? order by id desc",oldpassportNo).getStr("newpassportNo");
			String lastName = student.get(i).getStr("lastName");
			String firstName = student.get(i).getStr("firstName");
			String majorName = student.get(i).getStr("majorName");
			String eduStartDate = student.get(i).getStr("eduStartDate");
			String eduSeason = student.get(i).getStr("eduSeason");
			String dailyGrade = student.get(i).getStr("dailyGrade");
			String experimentalGrade = student.get(i).getStr("experimentalGrade");
			String otherGrade = student.get(i).getStr("otherGrade");
			String finalGrade = student.get(i).getStr("finalGrade");
			String grade = student.get(i).getStr("grade");



			HSSFCell cellfor11 = rowfor.createCell(0);
			HSSFCell cellfor12 = rowfor.createCell(1);
			HSSFCell cellfor13 = rowfor.createCell(2);
			HSSFCell cellfor14 = rowfor.createCell(3);
			HSSFCell cellfor15 = rowfor.createCell(4);
			HSSFCell cellfor16 = rowfor.createCell(5);
			HSSFCell cellfor17 = rowfor.createCell(6);
			HSSFCell cellfor18 = rowfor.createCell(7);
			HSSFCell cellfor19 = rowfor.createCell(8);
			HSSFCell cellfor110 = rowfor.createCell(9);
			HSSFCell cellfor111 = rowfor.createCell(10);
			HSSFCell cellfor112 = rowfor.createCell(11);
			//定义单元格字符串类型
			cellfor11.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor12.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor13.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor14.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor15.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor16.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor17.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor18.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor19.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor110.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor111.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor112.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellfor11.setCellValue(no);
			cellfor12.setCellValue(passportNo); 
			cellfor13.setCellValue(lastName);
			cellfor14.setCellValue(firstName);
			cellfor15.setCellValue(majorName);
			cellfor16.setCellValue(eduStartDate);
			cellfor17.setCellValue(eduSeason);
			cellfor18.setCellValue(dailyGrade);
			cellfor19.setCellValue(experimentalGrade);
			cellfor110.setCellValue(otherGrade);
			cellfor111.setCellValue(finalGrade);
			cellfor112.setCellValue(grade);}*/
		GetTimeUtil gt = new GetTimeUtil();

		Record testInfo = Db.findFirst("select * from test,teacher_info,course where test.courseNo=course.courseNo and test.tNo=teacher_info.tNo and testNo=?",testNo);

		String termVal = testInfo.getStr("termValue");
		String courseName = testInfo.getStr("courseName");
		String courseType = testInfo.getStr("courseType");
		String tName = testInfo.getStr("tName");
		String credit = testInfo.getInt("credit")+"";
		String time = gt.getTime2();

		//处理字符串
		String termValue = termVal.substring(0,4) + "-" + termVal.substring(5,9) + "学年第" + termVal.substring(10,11) + "学期";

		//创建一个Excel工作薄
		HSSFWorkbook book = new HSSFWorkbook();
		//在Excel工作薄中建立一张工作表
		HSSFSheet sheet = book.createSheet("学生成绩");

		//设置列宽
		sheet.setColumnWidth(0,9*210);
		sheet.setColumnWidth(1,27/2*255);
		sheet.setColumnWidth(2,22*276);
		sheet.setColumnWidth(3,8*266);
		sheet.setColumnWidth(4,5*287);
		sheet.setColumnWidth(5,6*287);
		sheet.setColumnWidth(6,6*287);
		sheet.setColumnWidth(7,5*290);
		sheet.setColumnWidth(8,5*290);
		sheet.setColumnWidth(9,5*295);


		//第一行设置
		HSSFCellStyle cellStyle = book.createCellStyle();	
		//设计这些格式
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		//设置字体
		HSSFFont font = book.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short)16);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
		cellStyle.setFont(font);

		//合并标题单元格
		CellRangeAddress cra1 = new CellRangeAddress(0, 0, 0, 9);
		Row row01 = sheet.createRow(0);
		sheet.addMergedRegion(cra1); 
		row01.setHeight((short) 465);
		Cell cell_00 = row01.createCell(0);
		cell_00.setCellStyle(cellStyle);
		cell_00.setCellValue("浙江工商大学考试成绩登记表");  

		//第二行设置
		HSSFCellStyle cellStyle2 = book.createCellStyle();	
		//设计这些格式
		cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		//设置字体
		HSSFFont font2 = book.createFont();
		font2.setFontName("Times New Roman");
		font2.setFontHeightInPoints((short)9);
		cellStyle2.setFont(font2);

		CellRangeAddress cra2 = new CellRangeAddress(1, 1, 0, 9);
		Row row2 = sheet.createRow(1);
		sheet.addMergedRegion(cra2);
		Cell cell_20 = row2.createCell(0);
		row2.setHeight((short) 330);
		cell_20.setCellStyle(cellStyle2);
		cell_20.setCellValue(termValue);  

		//文本设置
		HSSFCellStyle cellStyle3 = book.createCellStyle();	
		//设计这些格式
		cellStyle3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle3.setWrapText(true);


		HSSFFont font3 = book.createFont();
		font3.setFontName("宋体");
		font3.setFontHeightInPoints((short)8);
		cellStyle3.setFont(font3);
		CellRangeAddress cra3 = new CellRangeAddress(2,2,0,2);
		CellRangeAddress cra4 = new CellRangeAddress(2,2,3,5);
		CellRangeAddress cra5 = new CellRangeAddress(2,2,6,9);
		//		CellRangeAddress cra6 = new CellRangeAddress(2,2,7,9);
		CellRangeAddress cra7 = new CellRangeAddress(3,3,0,2);
		CellRangeAddress cra8 = new CellRangeAddress(3,3,3,5);
		CellRangeAddress cra9 = new CellRangeAddress(3,3,6,9);
		//		CellRangeAddress cra10 = new CellRangeAddress(3,3,7,9);
		sheet.addMergedRegion(cra2);
		sheet.addMergedRegion(cra3);
		sheet.addMergedRegion(cra4);
		sheet.addMergedRegion(cra5);
		//		sheet.addMergedRegion(cra6);
		sheet.addMergedRegion(cra7);
		sheet.addMergedRegion(cra8);
		sheet.addMergedRegion(cra9);
		//		sheet.addMergedRegion(cra10);

		Row row3 = sheet.createRow(2);
		row3.setHeight((short) 360);
		Cell cell_30 = row3.createCell(0);
		cell_30.setCellStyle(cellStyle3);
		cell_30.setCellValue("开课部门：计算机与信息工程学院");
		//		Cell cell_32 = row3.createCell(2);
		//		cell_32.setCellStyle(cellStyle3);
		//		cell_32.setCellValue("行政班：");
		Cell cell_35 = row3.createCell(3);
		cell_35.setCellStyle(cellStyle3);
		cell_35.setCellValue("任课教师："+tName);
		Cell cell_37 = row3.createCell(6);
		cell_37.setCellStyle(cellStyle3);
		cell_37.setCellValue("学分："+credit);

		Row row4 = sheet.createRow(3);
		row4.setHeight((short) 360);
		Cell cell_40 = row4.createCell(0);
		cell_40.setCellStyle(cellStyle3);
		cell_40.setCellValue("课程名称："+courseName);
		Cell cell_42 = row4.createCell(3);
		cell_42.setCellStyle(cellStyle3);
		cell_42.setCellValue("课程性质："+courseType);
		//		Cell cell_45 = row4.createCell(5);
		//		cell_45.setCellStyle(cellStyle3);
		//		cell_45.setCellValue("考核方式：");
		Cell cell_47 = row4.createCell(6);
		cell_47.setCellStyle(cellStyle3);
		cell_47.setCellValue("打印时间："+time);


		HSSFCellStyle cellStyle4 = book.createCellStyle();	
		//设计这些格式
		cellStyle4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		HSSFFont font4 = book.createFont();
		font4.setFontName("宋体");
		font4.setFontHeightInPoints((short)8);
		cellStyle4.setFont(font4);

		Row row5 = sheet.createRow(4);
		row5.setHeight((short) 345);
		Cell cell_50 = row5.createCell(0);
		cell_50.setCellStyle(cellStyle4);
		cell_50.setCellValue("序号");
		Cell cell_51 = row5.createCell(1);
		cell_51.setCellStyle(cellStyle4);
		cell_51.setCellValue("学号");
		Cell cell_52 = row5.createCell(2);
		cell_52.setCellStyle(cellStyle4);
		cell_52.setCellValue("姓名");
		Cell cell_53 = row5.createCell(3);
		cell_53.setCellStyle(cellStyle4);
		cell_53.setCellValue("班级");
		Cell cell_54 = row5.createCell(4);
		cell_54.setCellStyle(cellStyle4);
		cell_54.setCellValue("平时");
		Cell cell_55 = row5.createCell(5);
		cell_55.setCellStyle(cellStyle4);
		cell_55.setCellValue("期中");
		Cell cell_56 = row5.createCell(6);
		cell_56.setCellStyle(cellStyle4);
		cell_56.setCellValue("实验");
		Cell cell_57 = row5.createCell(7);
		cell_57.setCellStyle(cellStyle4);
		cell_57.setCellValue("期末");
		Cell cell_58 = row5.createCell(8);
		cell_58.setCellStyle(cellStyle4);
		cell_58.setCellValue("总评");
		Cell cell_59 = row5.createCell(9);
		cell_59.setCellStyle(cellStyle4);
		cell_59.setCellValue("备注");

		cellStyle4.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		cellStyle4.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		cellStyle4.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		cellStyle4.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 


		List<Record> stuGra = Db.find("select * from student_grade,student_info where student_grade.passportNo=student_info.passportNo and testNo=?",testNo);
		for(int i = 0;i < stuGra.size();i++){
			Row row = sheet.createRow(i+5);
			row.setHeight((short) 345);
			Cell cell_1 = row.createCell(0);
			cell_1.setCellStyle(cellStyle4);
			cell_1.setCellValue(i+1+"");
			Cell cell_2 = row.createCell(1);
			cell_2.setCellStyle(cellStyle4);
			cell_2.setCellValue(stuGra.get(i).getStr("sNo"));
			Cell cell_3 = row.createCell(2);
			cell_3.setCellStyle(cellStyle4);
			cell_3.setCellValue(stuGra.get(i).getStr("firstName") +" "+ stuGra.get(i).getStr("lastName"));
			Cell cell_4 = row.createCell(3);
			cell_4.setCellStyle(cellStyle4);
			cell_4.setCellValue(stuGra.get(i).getStr("class"));
			Cell cell_5 = row.createCell(4);
			cell_5.setCellStyle(cellStyle4);
			cell_5.setCellValue(stuGra.get(i).getStr("dailyGrade"));
			Cell cell_6 = row.createCell(5);
			cell_6.setCellStyle(cellStyle4);
			cell_6.setCellValue(stuGra.get(i).getStr("experimentalGrade"));
			Cell cell_7 = row.createCell(6);
			cell_7.setCellStyle(cellStyle4);
			cell_7.setCellValue(stuGra.get(i).getStr("otherGrade"));
			Cell cell_8 = row.createCell(7);
			cell_8.setCellStyle(cellStyle4);
			cell_8.setCellValue(stuGra.get(i).getStr("finalGrade"));
			Cell cell_9 = row.createCell(8);
			cell_9.setCellStyle(cellStyle4);
			cell_9.setCellValue(stuGra.get(i).getStr("grade"));
			String temp = "";
			if(stuGra.get(i).getStr("finalGrade").equals("作弊") || stuGra.get(i).getStr("finalGrade").equals("旷考")){
				temp = stuGra.get(i).getStr("finalGrade");
			}
			Cell cell_10 = row.createCell(9);
			cell_10.setCellStyle(cellStyle4);
			cell_10.setCellValue(temp);

			cellStyle4.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
			cellStyle4.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
			cellStyle4.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
			cellStyle4.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		}

		try {
			//写入数据 把相应的Excel工作薄存盘
			book.write(out);
			System.out.println("game over!!!");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	//导出补考成绩
	public void exportReplace(String testNo, OutputStream out) {
		GetTimeUtil gt = new GetTimeUtil();

		Record testInfo = Db.findFirst("select * from test,teacher_info,course where test.courseNo=course.courseNo and test.tNo=teacher_info.tNo and testNo=?",testNo);

		String termVal = testInfo.getStr("termValue");
		String courseName = testInfo.getStr("courseName");
		String courseType = testInfo.getStr("courseType");
		String tName = testInfo.getStr("tName");
		String credit = testInfo.getInt("credit")+"";
		String time = gt.getTime2();

		//处理字符串
		String termValue = termVal.substring(0,4) + "-" + termVal.substring(5,9) + "学年第" + termVal.substring(10,11) + "学期";

		//创建一个Excel工作薄
		HSSFWorkbook book = new HSSFWorkbook();
		//在Excel工作薄中建立一张工作表
		HSSFSheet sheet = book.createSheet("学生成绩");

		//设置列宽
		sheet.setColumnWidth(0,10*210);
		sheet.setColumnWidth(1,17*255);
		sheet.setColumnWidth(2,28*276);
		sheet.setColumnWidth(3,19/2*266);
		sheet.setColumnWidth(4,13*287);
		sheet.setColumnWidth(5,10*287);


		//第一行设置
		HSSFCellStyle cellStyle = book.createCellStyle();	
		//设计这些格式
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		//设置字体
		HSSFFont font = book.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short)16);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
		cellStyle.setFont(font);

		//合并标题单元格
		CellRangeAddress cra1 = new CellRangeAddress(0, 0, 0, 5);
		Row row01 = sheet.createRow(0);
		sheet.addMergedRegion(cra1); 
		row01.setHeight((short) 465);
		Cell cell_00 = row01.createCell(0);
		cell_00.setCellStyle(cellStyle);
		cell_00.setCellValue("浙江工商大学补考考试成绩登记表");  

		//第二行设置
		HSSFCellStyle cellStyle2 = book.createCellStyle();	
		//设计这些格式
		cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		//设置字体
		HSSFFont font2 = book.createFont();
		font2.setFontName("Times New Roman");
		font2.setFontHeightInPoints((short)9);
		cellStyle2.setFont(font2);

		CellRangeAddress cra2 = new CellRangeAddress(1, 1, 0, 5);
		Row row2 = sheet.createRow(1);
		sheet.addMergedRegion(cra2);
		Cell cell_20 = row2.createCell(0);
		row2.setHeight((short) 330);
		cell_20.setCellStyle(cellStyle2);
		cell_20.setCellValue(termValue);  

		//文本设置
		HSSFCellStyle cellStyle3 = book.createCellStyle();	
		//设计这些格式
		cellStyle3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		cellStyle3.setWrapText(true);


		HSSFFont font3 = book.createFont();
		font3.setFontName("宋体");
		font3.setFontHeightInPoints((short)8);
		cellStyle3.setFont(font3);
		CellRangeAddress cra3 = new CellRangeAddress(2,2,0,1);
		CellRangeAddress cra4 = new CellRangeAddress(2,2,2,3);
		CellRangeAddress cra5 = new CellRangeAddress(2,2,4,5);
		CellRangeAddress cra7 = new CellRangeAddress(3,3,0,1);
		CellRangeAddress cra8 = new CellRangeAddress(3,3,2,3);
		CellRangeAddress cra9 = new CellRangeAddress(3,3,4,5);
		sheet.addMergedRegion(cra3);
		sheet.addMergedRegion(cra4);
		sheet.addMergedRegion(cra5);
		sheet.addMergedRegion(cra7);
		sheet.addMergedRegion(cra8);
		sheet.addMergedRegion(cra9);

		Row row3 = sheet.createRow(2);
		row3.setHeight((short) 360);
		Cell cell_30 = row3.createCell(0);
		cell_30.setCellStyle(cellStyle3);
		cell_30.setCellValue("开课部门：计算机与信息工程学院");
		Cell cell_32 = row3.createCell(2);
		cell_32.setCellStyle(cellStyle3);
		cell_32.setCellValue("任课教师："+tName);
//		Cell cell_35 = row3.createCell(4);
//		cell_35.setCellStyle(cellStyle3);
//		cell_35.setCellValue();
		Cell cell_37 = row3.createCell(4);
		cell_37.setCellStyle(cellStyle3);
		cell_37.setCellValue("学分："+credit);

		Row row4 = sheet.createRow(3);
		row4.setHeight((short) 360);
		Cell cell_40 = row4.createCell(0);
		cell_40.setCellStyle(cellStyle3);
		cell_40.setCellValue("课程名称："+courseName);
		Cell cell_42 = row4.createCell(2);
		cell_42.setCellStyle(cellStyle3);
		cell_42.setCellValue("课程性质："+courseType);
//		Cell cell_45 = row4.createCell(4);
//		cell_45.setCellStyle(cellStyle3);
//		cell_45.setCellValue("考核方式：");
		Cell cell_47 = row4.createCell(4);
		cell_47.setCellStyle(cellStyle3);
		cell_47.setCellValue("打印时间"+time);


		HSSFCellStyle cellStyle4 = book.createCellStyle();	
		//设计这些格式
		cellStyle4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		HSSFFont font4 = book.createFont();
		font4.setFontName("宋体");
		font4.setFontHeightInPoints((short)8);
		cellStyle4.setFont(font4);

		Row row5 = sheet.createRow(4);
		row5.setHeight((short) 345);
		Cell cell_50 = row5.createCell(0);
		cell_50.setCellStyle(cellStyle4);
		cell_50.setCellValue("序号");
		Cell cell_51 = row5.createCell(1);
		cell_51.setCellStyle(cellStyle4);
		cell_51.setCellValue("学号");
		Cell cell_52 = row5.createCell(2);
		cell_52.setCellStyle(cellStyle4);
		cell_52.setCellValue("姓名");
		Cell cell_53 = row5.createCell(3);
		cell_53.setCellStyle(cellStyle4);
		cell_53.setCellValue("班级");
		Cell cell_58 = row5.createCell(4);
		cell_58.setCellStyle(cellStyle4);
		cell_58.setCellValue("总评");
		Cell cell_59 = row5.createCell(5);
		cell_59.setCellStyle(cellStyle4);
		cell_59.setCellValue("备注");

		cellStyle4.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		cellStyle4.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		cellStyle4.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		cellStyle4.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 


		List<Record> stuGra = Db.find("select * from student_grade,student_info where student_grade.passportNo=student_info.passportNo and testNo=?",testNo);
		for(int i = 0;i < stuGra.size();i++){
			Row row = sheet.createRow(i+5);
			row.setHeight((short) 345);
			Cell cell_1 = row.createCell(0);
			cell_1.setCellStyle(cellStyle4);
			cell_1.setCellValue(i+1+"");
			Cell cell_2 = row.createCell(1);
			cell_2.setCellStyle(cellStyle4);
			cell_2.setCellValue(stuGra.get(i).getStr("sNo"));
			Cell cell_3 = row.createCell(2);
			cell_3.setCellStyle(cellStyle4);
			cell_3.setCellValue(stuGra.get(i).getStr("firstName") + stuGra.get(i).getStr("lastName"));
			Cell cell_4 = row.createCell(3);
			cell_4.setCellStyle(cellStyle4);
			cell_4.setCellValue("class");
			Cell cell_9 = row.createCell(4);
			cell_9.setCellStyle(cellStyle4);
			String temp = "";
			if(stuGra.get(i).getStr("grade").equals("作弊") || stuGra.get(i).getStr("grade").equals("旷考")){
				temp = stuGra.get(i).getStr("grade");
				cell_9.setCellValue("0");
			}else{
				cell_9.setCellValue(stuGra.get(i).getStr("grade"));
			}

			Cell cell_10 = row.createCell(5);
			cell_10.setCellStyle(cellStyle4);
			cell_10.setCellValue(temp);

			cellStyle4.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
			cellStyle4.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
			cellStyle4.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
			cellStyle4.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		}

		try {
			//写入数据 把相应的Excel工作薄存盘
			book.write(out);
			System.out.println("game over!!!");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	//课程超时判断
	public void timeJudge() {
		Date date = new Date();
		DateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");
		List<Record> test = Db.find("select * from test,course_end where test.testNo=course_end.testNo");
		for(int i = 0;i < test.size();i++){
			String tiemtemp = test.get(i).getStr("endDate");
			String testNo = test.get(i).getStr("testNo");
			String testLock = test.get(i).getStr("testLock");
			try {
				java.util.Date courseTime = formatter.parse(tiemtemp);
				boolean temp = courseTime.before(date);
				if(temp){
					if(testLock.equals("0") || testLock.equals("3")){
						Db.update("update test set testLock='2' where testNo=?",testNo);
					}
				}
				System.out.println(temp);
				System.out.println(testNo);
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				System.out.println("考试时间未设置。");
			} 
		}
	}

	//教师模板下载
	public void gradeModel(String tNo, String testNo,OutputStream out) {

		//	 	System.out.println("--------------------");
		//创建一个Excel工作薄
		HSSFWorkbook book = new HSSFWorkbook();
		//在Excel工作薄中建立一张工作表
		HSSFSheet sheet = book.createSheet("学生成绩导入模板");
		//设置单元格格式（文本）
		HSSFCellStyle cellStyle = book.createCellStyle();	
		//		System.out.println("----------1----------");
		//设计这些格式
		//		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		//		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		//		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		//		cellStyle.setWrapText(true);//自动换行
		//		System.out.println("-----------2---------");
		//设置字体
		HSSFFont font = book.createFont();
		//		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
		cellStyle.setFont(font);
		//		System.out.println("-----------3---------");

		HSSFCellStyle cellStyle1 = book.createCellStyle();
		//		cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直  
		//		cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		//		cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);//居中
		//		cellStyle.setWrapText(true);//自动换行

		//拿到相应的数据
		Record model = Db.findFirst("select * from test,course,teacher_info where test.courseNo=course.courseNo and test.tNo=teacher_info.tNo and testNo=?",testNo);
		String uuid = model.getStr("uuid");
		System.out.println(uuid+"bbbbbbbbbbbb");
		//第一行为标题
		HSSFRow row0 = sheet.createRow(0);//创建第一行
		//		row0.setHeight((short) (1.5*256));//行高
		HSSFCell cell01 = row0.createCell(0);
		HSSFCell cell02 = row0.createCell(1);
		HSSFCell cell03 = row0.createCell(3);
		HSSFCell cell04 = row0.createCell(4);
		HSSFCell cell05 = row0.createCell(6);
		HSSFCell cell06 = row0.createCell(7);
		HSSFCell cell07 = row0.createCell(9);
		HSSFCell cell08 = row0.createCell(10);
		HSSFCell cell09 = row0.createCell(11);
		//定义单元格字符串类型
		cell01.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell02.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell03.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell04.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell05.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell06.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell07.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell08.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell09.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell01.setCellStyle(cellStyle);
		cell02.setCellStyle(cellStyle);
		cell03.setCellStyle(cellStyle);
		cell04.setCellStyle(cellStyle);
		cell05.setCellStyle(cellStyle);
		cell06.setCellStyle(cellStyle);
		cell07.setCellStyle(cellStyle);
		cell08.setCellStyle(cellStyle);
		cell09.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell01.setCellValue("学期");
		cell02.setCellValue(model.getStr("termValue").toString()); 
		cell03.setCellValue("课程名");
		cell04.setCellValue(model.getStr("courseName").toString());
		cell05.setCellValue("教师");
		cell06.setCellValue(model.getStr("tName").toString());
		//		cell07.setCellValue("识别码");
		//		cell08.setCellValue(model.getStr("testNo").toString());
		//		cell09.setCellValue("请不要改此行数据。");
		//第二行 获取标题对应的数据
		HSSFRow row1 = sheet.createRow(1);//创建第二行
		HSSFCell cell11 = row1.createCell(0);
		HSSFCell cell12 = row1.createCell(3);
		HSSFCell cell13 = row1.createCell(4);
		HSSFCell cell14 = row1.createCell(5);
		HSSFCell cell15 = row1.createCell(6);
		HSSFCell cell16 = row1.createCell(7);
		HSSFCell cell17 = row1.createCell(8);
		HSSFCell cell18 = row1.createCell(9);
		HSSFCell cell19 = row1.createCell(10);
		//定义单元格字符串类型
		cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell12.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell13.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell14.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell15.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell16.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell17.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell18.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell19.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell11.setCellStyle(cellStyle);
		cell12.setCellStyle(cellStyle);
		cell13.setCellStyle(cellStyle);
		cell14.setCellStyle(cellStyle);
		cell15.setCellStyle(cellStyle);
		cell16.setCellStyle(cellStyle);
		cell17.setCellStyle(cellStyle);
		cell18.setCellStyle(cellStyle);
		cell19.setCellStyle(cellStyle);
		//在单元格中输入数据
		//		String temp = record.getStr("tName").replace(",","");
		cell11.setCellValue("请输入成绩比例");
		cell12.setCellValue("平时成绩"); 
		cell13.setCellValue("25");
		cell14.setCellValue("实验成绩");
		cell15.setCellValue("25");
		cell16.setCellValue("其他成绩");
		cell17.setCellValue("25");
		cell18.setCellValue("期末成绩");
		cell19.setCellValue("25");

		//		System.out.println("---------5-----------");
		//第三行
		HSSFRow row2 = sheet.createRow(2);//创建第三行
		//		row2.setHeight((short) (3*256));//行高
		HSSFCell cell20 = row2.createCell(0);
		HSSFCell cell21 = row2.createCell(1);
		HSSFCell cell22 = row2.createCell(2);
		HSSFCell cell23 = row2.createCell(3);
		HSSFCell cell24 = row2.createCell(4);
		HSSFCell cell25 = row2.createCell(5);
		HSSFCell cell26 = row2.createCell(6);
		//定义单元格字符串类型
		cell20.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell21.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell22.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell23.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell24.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell25.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell26.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell20.setCellStyle(cellStyle);
		cell21.setCellStyle(cellStyle);
		cell22.setCellStyle(cellStyle);
		cell23.setCellStyle(cellStyle);
		cell24.setCellStyle(cellStyle);
		cell25.setCellStyle(cellStyle);
		cell26.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell20.setCellValue("序号");
		cell21.setCellValue("学号");
		cell22.setCellValue("护照号"); 
		cell23.setCellValue("平时成绩");
		cell24.setCellValue("实验成绩"); 
		cell25.setCellValue("其他成绩"); 
		cell26.setCellValue("期末成绩"); 
		//循环数据到excel中
		List<Record> student = Db.find("select * from roster where uuid=?",uuid);
		for(int i = 0;i < student.size();i++){
			HSSFRow row = sheet.createRow(i+3);

			HSSFCell cell0 = row.createCell(0);
			HSSFCell cell1 = row.createCell(1);
			HSSFCell cell2 = row.createCell(2);
			//定义单元格字符串类型
			cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			//设计格式
			cell0.setCellStyle(cellStyle);
			cell1.setCellStyle(cellStyle);
			cell2.setCellStyle(cellStyle);
			//在单元格中输入数据
			String temp = student.get(i).getStr("passportNo");
			String newpassportNo = Db.findFirst("select * from newpassportno where passportNo=? order by id desc",temp).getStr("newpassportNo");
			cell0.setCellValue(i+1);
			cell1.setCellValue(student.get(i).getStr("sNo"));
			cell2.setCellValue(newpassportNo); 
		}
		//		System.out.println("----------7----------");
		try {
			//写入数据 把相应的Excel工作薄存盘
			book.write(out);

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}


	}
	//教师补考成绩导入模板下载
	public void gradePreModel(String tNo, String testNo,OutputStream out) {
		HSSFWorkbook book = new HSSFWorkbook();
		HSSFSheet sheet = book.createSheet("学生成绩导入模板");
		HSSFCellStyle cellStyle = book.createCellStyle();	
		HSSFFont font = book.createFont();
		cellStyle.setFont(font);
		HSSFCellStyle cellStyle1 = book.createCellStyle();

		//拿到相应的数据
		Record model = Db.findFirst("select * from test,course,teacher_info where test.courseNo=course.courseNo and test.tNo=teacher_info.tNo and testNo=?",testNo);
		String uuid = model.getStr("uuid");
		//第一行为标题
		HSSFRow row0 = sheet.createRow(0);//创建第一行
		HSSFCell cell01 = row0.createCell(0);
		HSSFCell cell02 = row0.createCell(1);
		HSSFCell cell03 = row0.createCell(3);
		HSSFCell cell04 = row0.createCell(4);
		HSSFCell cell05 = row0.createCell(6);
		HSSFCell cell06 = row0.createCell(7);
		//定义单元格字符串类型
		cell01.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell02.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell03.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell04.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell05.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell06.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell01.setCellStyle(cellStyle);
		cell02.setCellStyle(cellStyle);
		cell03.setCellStyle(cellStyle);
		cell04.setCellStyle(cellStyle);
		cell05.setCellStyle(cellStyle);
		cell06.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell01.setCellValue("学期");
		cell02.setCellValue(model.getStr("termValue").toString()); 
		cell03.setCellValue("课程名");
		cell04.setCellValue(model.getStr("courseName").toString());
		cell05.setCellValue("教师");
		cell06.setCellValue(model.getStr("tName").toString());
		//第二行
		HSSFRow row2 = sheet.createRow(1);
		HSSFCell cell20 = row2.createCell(0);
		HSSFCell cell21 = row2.createCell(1);
		HSSFCell cell22 = row2.createCell(2);
		HSSFCell cell23 = row2.createCell(3);
		//定义单元格字符串类型
		cell20.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell21.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell22.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell23.setCellType(HSSFCell.CELL_TYPE_STRING);
		//设计格式
		cell20.setCellStyle(cellStyle);
		cell21.setCellStyle(cellStyle);
		cell22.setCellStyle(cellStyle);
		cell23.setCellStyle(cellStyle);
		//在单元格中输入数据
		cell20.setCellValue("序号");
		cell21.setCellValue("学号");
		cell22.setCellValue("护照号"); 
		cell23.setCellValue("成绩");
		//循环数据到excel中
		List<Record> student = Db.find("select * from student_grade,student_info where student_grade.passportNo=student_info.passportNo and testNo=?",testNo);
		for(int i = 0;i < student.size();i++){
			HSSFRow row = sheet.createRow(i+2);

			HSSFCell cell0 = row.createCell(0);
			HSSFCell cell1 = row.createCell(1);
			HSSFCell cell2 = row.createCell(2);
			//定义单元格字符串类型
			cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			//设计格式
			cell0.setCellStyle(cellStyle);
			cell1.setCellStyle(cellStyle);
			cell2.setCellStyle(cellStyle);
			//在单元格中输入数据
			String temp = student.get(i).getStr("passportNo");
			String newpassportNo = Db.findFirst("select * from newpassportno where passportNo=? order by id desc",temp).getStr("newpassportNo");
			cell0.setCellValue(i+1);
			cell1.setCellValue(student.get(i).getStr("sNo"));
			cell2.setCellValue(newpassportNo); 
		}
		try {
			book.write(out);

		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}



	}

	public String importGrade(InputStream is,String testNo) {
		POIFSFileSystem fs;
		HSSFWorkbook wb;
		HSSFSheet sheet;
		HSSFRow row;
		List<Record> stuGra;
		String error = "" ;
		try {
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
			stuGra = new ArrayList<Record>();
			sheet = wb.getSheetAt(0);
			row = sheet.getRow(0);

			int colNum = 0;
			int rowNum = 0;
			colNum = row.getPhysicalNumberOfCells();//总列数
			rowNum = sheet.getPhysicalNumberOfRows();//总行数
			//拿到相应数据
			//	        String testNo = (sheet.getRow(0).getCell(10)).toString();
			//	        System.out.println("考试号："+testNo);
			Record info = Db.findFirst("select * from test,course where test.courseNo=course.courseNo and test.testNo=?",testNo);
			//比例设定
			HSSFRow rowbi = sheet.getRow(1);
			for(int i = 4; i < 11; i++){
				HSSFCell cellbi = rowbi.getCell(i);//假如row.getCell(0)中的数值为12345678910123  
				cellbi.setCellType(HSSFCell.CELL_TYPE_STRING);  
			}
			String oneb = (sheet.getRow(1).getCell(4)).toString();
			System.out.println("比例："+oneb);
			String twob = (sheet.getRow(1).getCell(6)).toString();
			String thrb = (sheet.getRow(1).getCell(8)).toString();
			String foub = (sheet.getRow(1).getCell(10)).toString();
			int one = Integer.valueOf(oneb);
			int two = Integer.valueOf(twob);
			int thr = Integer.valueOf(thrb);
			int fou = Integer.valueOf(foub);


			//循环遍历
			Db.update("delete from student_grade where testNo=?",testNo);
			for (int j = 3; j < rowNum; j++) {
				String finalGrade = "";
				HSSFRow rowj = sheet.getRow(j);
				HSSFCell cell = null  ;
				for(int i = 1; i < 7; i++){
					cell = rowj.getCell(i);//假如row.getCell(0)中的数值为12345678910123  
					if(cell != null){
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
					}
				}
				String newpassportNo =  (sheet.getRow(j).getCell(2)).toString();
				String passportNo = Db.findFirst("select * from newpassportno where newpassportNo=? order by id desc",newpassportNo).getStr("passportNo");
				String daily = "";
				String exper = "";
				String other = "";
				String finall = "";
				if(one != 0){
					if((sheet.getRow(j).getCell(3)) == null){
						daily = "";
					}else{
						daily = (sheet.getRow(j).getCell(3)).toString();
					}

				}
				if(two != 0){
					if((sheet.getRow(j).getCell(4)) == null){
						exper = "";
					}else{
						exper = (sheet.getRow(j).getCell(4)).toString();
					}
				}
				if(thr != 0){
					if((sheet.getRow(j).getCell(5)) == null){
						other = "";
					}else{
						other = (sheet.getRow(j).getCell(5)).toString();
					}
				}
				if(fou != 0){
					if((sheet.getRow(j).getCell(6)) == null){
						finall = "";
					}else{
						finall = (sheet.getRow(j).getCell(6)).toString();
					}
				}
				String grade[] = {daily,exper,other,finall};
				if(one == 0){
					grade[0] = "0";
					daily = "";
				}
				if(two == 0){
					grade[1] = "0";
					exper = "";
				}
				if(thr == 0){
					grade[2] = "0";
					other = "";
				}
				if(fou == 0){
					grade[3] = "0";
					finall = "";
				}
				Record stuinfo = Db.findFirst("select * from student_info where passportNo=?",passportNo);
				if(daily.equals("A") || daily.equals("B") || daily.equals("C") ||daily.equals("P") ||daily.equals("F")||
						exper.equals("A") || exper.equals("B") || exper.equals("C") ||exper.equals("P") ||exper.equals("F")||
						other.equals("A") || other.equals("B") || other.equals("C") ||other.equals("P") ||other.equals("F")||
						finall.equals("A") || finall.equals("B") || finall.equals("C") ||finall.equals("P") ||finall.equals("F")){
					System.out.println("五级制");
					int intGrade[] = new int[4];
					int gradeTemp = 0;
					for(int x = 0;x < 4;x++){
						switch(grade[x]){
						case "A" :
							grade[x] = "90";
							break;
						case "B" :
							grade[x] = "80";
							break;
						case "C" :
							grade[x] = "70";
							break;
						case "P" :
							grade[x] = "60";
							break;
						case "F":
							grade[x] = "50";
							break;
						case "作弊":
							finalGrade = "F";
							continue;
						case "旷考":
							grade[x] = "0";
							break;
						case "0":
							grade[x] = "0";
							break;
						default :
							grade[x] = "";
							break;
						}
						try{
							gradeTemp = Integer.valueOf(grade[x]);
							intGrade[x] = gradeTemp;
						}catch(NumberFormatException e){
							finalGrade = "未录入";
						}
					}
					if(finalGrade.equals("")){

						int finalGra = (one*intGrade[0] + two*intGrade[1] + thr*intGrade[2] + fou*intGrade[3])/100;
						System.out.println("最后的成绩:"+finalGra);
						if(finalGrade.equals("")){
							if( finalGra >= 90){
								finalGrade = "A";
							}else if(finalGra >= 80){
								finalGrade = "B";
							}else if(finalGra >= 70){
								finalGrade = "C";
							}else if(finalGra >= 60){
								finalGrade = "P";
							}else if(finalGra < 60){
								finalGrade = "F";
							}
						}

					}
				}else{
					System.out.println("百分制");
					try{
						int intGrade[] = new int[4];
						if(grade[3].equals("旷考")){
							grade[3] = "0";
						}
						if(!grade[3].equals("作弊")){
							for(int x = 0;x < grade.length;x++){
								intGrade[x] = Integer.valueOf(grade[x]);
							}
							int finalGra = (one*intGrade[0] + two*intGrade[1] + thr*intGrade[2] + fou*intGrade[3])/100;
							finalGrade = finalGra + "";
						}else{
							finalGrade = "0";
						}

					}catch(NumberFormatException e){
						finalGrade = "未录入";
					}
				}

				System.out.println("finalGrade:"+finalGrade);
				if(finalGrade.equals("未录入")){
					error = "存在未录入成绩。";
				}
				System.out.println(info);
				Record stu = new Record().set("termValue",info.getStr("termValue")).set("passportNo",passportNo)
						.set("majorNo",stuinfo.getStr("majorNo")).set("majorName",stuinfo.getStr("majorName"))
						.set("courseNo",info.getStr("courseNo")).set("courseName",info.getStr("courseName"))
						.set("dailyGrade",daily).set("experimentalGrade",exper)
						.set("otherGrade",other).set("finalGrade",finall)
						.set("dailyPercent",oneb).set("experimentalPercent",twob)
						.set("otherPercent",thrb).set("finalPercent",foub)
						.set("tNo",info.getStr("tNo")).set("gradeStatus","0")
						.set("uuid",info.getStr("uuid")).set("testNo",testNo).set("grade",finalGrade);
				stuGra.add(stu);

				Db.save("student_grade", stu);
				Db.update("update test set testLock='3' where testNo=?",testNo);
			}

		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				is.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return error;
	}

	//导入补考成绩
	public String importPreGrade(InputStream is,String testNo) {
		POIFSFileSystem fs;
		HSSFWorkbook wb;
		HSSFSheet sheet;
		HSSFRow row;
		List<Record> stuGra;
		String error = "";
		try {
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
			stuGra = new ArrayList<Record>();
			sheet = wb.getSheetAt(0);
			row = sheet.getRow(0);
			int colNum = 0;
			int rowNum = 0;
			colNum = row.getPhysicalNumberOfCells();//总列数
			rowNum = sheet.getPhysicalNumberOfRows();//总行数
			Record info = Db.findFirst("select * from test,course where test.courseNo=course.courseNo and test.testNo=?",testNo);

			//循环遍历
			Db.update("delete from student_grade where testNo=?",testNo);
			for (int j = 2; j < rowNum; j++) {
				HSSFRow rowj = sheet.getRow(j);
				HSSFCell cell = null  ;
				for(int i = 2; i < 4; i++){
					cell = rowj.getCell(i);
					if(cell != null){
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
					}
					
				}
				String newpassportNo =  (sheet.getRow(j).getCell(2)).toString();
				String grade = "";
				if(sheet.getRow(j).getCell(3) != null){
					grade =  (sheet.getRow(j).getCell(3)).toString();
				}
				String passportNo = Db.findFirst("select * from newpassportno where newpassportNo=? order by id desc",newpassportNo).getStr("passportNo");

				Record stuinfo = Db.findFirst("select * from student_info where passportNo=?",passportNo);
				if(grade.equals("")){
					error = "存在未录入成绩。";
				}
				Record stu = new Record().set("termValue",info.getStr("termValue")).set("passportNo",passportNo)
						.set("majorNo",stuinfo.getStr("majorNo")).set("majorName",stuinfo.getStr("majorName"))
						.set("courseNo",info.getStr("courseNo")).set("courseName",info.getStr("courseName"))
						.set("tNo",info.getStr("tNo")).set("gradeStatus","0")
						.set("uuid",info.getStr("uuid")).set("testNo",testNo).set("grade",grade);
				stuGra.add(stu);

				Db.save("student_grade", stu);
				Db.update("update test set testLock='3' where testNo=?",testNo);
			}

		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				is.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return error;
	}

}

